package demo_02;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BookinfoDao_01 {
    public static void main(String[] args) {
        BookinfoDao_01 book = new BookinfoDao_01();
        /*String sql = "INSERT INTO tbl_book_info(b_no,b_name,b_price)\n" +
                "VALUES('250','trump',250)";
        int r=book.executeUpdate(sql);*/
        String sql = "select b_id,b_name from tbl_book_info";
        List<Map<String,Object>>books = book.executeQuery(sql);
        System.out.println(books);
        /*if (r>0){
            System.out.println("执行成功，受影响的行为："+r);
        }else{
            System.out.println("执行失败！");
        }*/
    }

    /**
     *
     * @param sql
     * @return
     * Map<String,Object>对应表中的一行数据!@#$%^&*()_+
     */
    public List<Map<String,Object>> executeQuery(String sql){
        List<Map<String,Object>> list = new ArrayList<Map<String, Object>>();
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String password = "admin";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rst = null;
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动类不存在!");
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            rst = stmt.executeQuery(sql);
            //获取查询结果集中的列信息
            ResultSetMetaData meta = rst.getMetaData();
            //查询了几列
            int c =meta.getColumnCount();
            while (rst.next()){
                Map<String,Object> book = new HashMap<>();
                for (int i =0;i<c;i++){
                    String key = meta.getColumnLabel(i+1);
                    Object value = rst.getObject(i+1);
                    book.put(key,value);
                }
                list.add(book);
            }

        } catch (
                SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return list;

    }

    public int executeUpdate(String sql) {
        int r = 0;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String password = "admin";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动类不存在!");
            e.printStackTrace();
        }
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            r = stmt.executeUpdate(sql);//执行修改,返回影响行数
        } catch (
                SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return r;

    }
}
